package com.jiepos.mpos.biz.review.service;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;

import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;

import com.jiepos.mpos.common.constant.DBConstant.Table;
import com.jiepos.mpos.common.constant.SystemConstant;
import com.jiepos.mpos.common.dao.MemReviewDao;
import com.jiepos.mpos.common.entity.MemReviewEntity;
import com.jiepos.mpos.core.entity.Pagination;
import com.jiepos.mpos.core.service.BaseService;
import com.jiepos.mpos.core.util.StringUtils;

/**
 * @ClassName: MemReviewDoneService
 * @Description: 审核管理服务层
 * @author 张磊
 * @date 2017年9月21日 下午3:08:25
 */
@Service
public class MemReviewDoneService extends BaseService {

	@Autowired
	private MemReviewDao memReviewDao;
	
	/**
	 * 查询实名认证已办事项列表json分页数据
	 * @param pagination 分页对象
	 * @param request
	 * @param memReviewEntity 实名认证实体对象
	 * @return
	 */
	
	public Map getMemReviewDoneList(Pagination pagination, HttpServletRequest request,MemReviewEntity memReviewEntity){
		
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT aa.*, bb.audit_comments FROM ( ")
			.append("SELECT t1.CUSTOM_UUID, t1.CUSTOM_UUID AS UUID,t1.CUSTOM_NO,CONCAT(RPAD (SUBSTR (t1.PHONE, 1, 3),LENGTH (t1.PHONE) - 4,'*'),SUBSTR (t1.PHONE,LENGTH (t1.PHONE) - 3,4)) phone,t1.NAME,")
			.append("NVL2(t2.login_id,CONCAT(RPAD (SUBSTR (t2.login_id, 1, 3),LENGTH (t2.login_id) - 4,'*'),SUBSTR (t2.login_id,LENGTH (t2.login_id) - 3,4))||'-'||t2.NAME,'') SUPCUSTOM,")
			.append("t3.ins_uuid,t3.ins_cd,t3.ins_cn_nm,NVL2(t3.ins_cd,t3.ins_cd || '-' || t3.ins_cn_nm,'') as insname,")
			.append("NVL2(t4.area_cd,t4.area_cd || '-' || t4.area_cn_nm, '') as areaname,t1.REALNAME_AUTH_ST,t1.REALNAME_AUDIT_ST,t1.REC_ST,")
			.append("CONCAT (RPAD (SUBSTR (t1.CERTIFICATE_NO, 1, 6),LENGTH (t1.CERTIFICATE_NO) - 4,'*'),SUBSTR (t1.CERTIFICATE_NO,LENGTH (t1.CERTIFICATE_NO) - 6,4)) AS CERTIFICATE_NO,")
			.append("TO_CHAR (t1.rec_crt_ts,'yyyy-mm-dd hh24:mi:ss') AS rec_crt_ts,TO_CHAR (t1.rec_upd_ts,'yyyy-mm-dd hh24:mi:ss') AS rec_upd_ts,")
			.append("t1.rec_upd_usr_id FROM ").append(Table.T_APP_CUSTOM_INF).append(" t1 LEFT JOIN ").append(Table.T_APP_CUSTOM_INF)
			.append(" t2 ON t1.SUP_CUSTOM_UUID = t2.CUSTOM_UUID LEFT JOIN ")
			.append(Table.T_APP_INS_INF).append("  t3 ON t1.ins_uuid = t3.ins_uuid LEFT JOIN ")
			.append(Table.T_APP_AREA_CD).append(" t4 ON t1.area_uuid = t4.area_uuid WHERE 1 = 1 AND t1.REALNAME_AUDIT_ST IN ('"+SystemConstant.REVIEW_ST_1+"','"+ SystemConstant.REVIEW_ST_2+"') ) aa ")
			.append(" LEFT JOIN (SELECT CUSTOM_UUID,(SELECT AUDIT_COMMENTS FROM ")
			.append(" (SELECT AUDIT_COMMENTS,SYSTEM_TYPE,SYSTEM_KEY,STATE_OPERATE FROM ").append(Table.T_APP_WORKFLOW_TRACE).append(" ORDER BY REC_CRT_TS DESC )")
			.append(" WHERE SYSTEM_TYPE = 'T_APP_CUSTOM_INF' AND STATE_OPERATE IN ")
			.append(" ('"+SystemConstant.STATE_CHANGE_1+"','"+ SystemConstant.STATE_CHANGE_2+"') AND SYSTEM_KEY = CUSTOM_UUID AND ROWNUM <= 1) AS AUDIT_COMMENTS FROM ")
			.append(Table.T_APP_CUSTOM_INF).append(" ) bb ON aa.uuid = bb.CUSTOM_UUID WHERE 1 = 1 ");
		List list = new ArrayList();
		if (!StringUtils.isBlank(memReviewEntity.getCustom_no())) {
			sb.append( " and custom_no like ? " );
			list.add("%" + memReviewEntity.getCustom_no() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getPhone())) {
			sb.append( " and phone like ? " );
			list.add("%" + memReviewEntity.getPhone() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getName())) {
			sb.append( " and name like ? " );
			list.add("%" + memReviewEntity.getName() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getIns_cd())) {
			sb.append( " and ins_cd like ? " );
			list.add("%" + memReviewEntity.getIns_cd() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getIns_cn_nm())) {
			sb.append( " and ins_cn_nm like ? " );
			list.add("%" + memReviewEntity.getIns_cn_nm() + "%");
		}
		if (!StringUtils.isBlank(memReviewEntity.getCertificate_no())) {
			sb.append( " and certificate_no like ? " );
			list.add("%" + memReviewEntity.getCertificate_no() + "%");
		}
		pagination.queryString = sb.toString();
		pagination = super.getCustomAuthSql(request, pagination, "aa", null);
		pagination.queryString = pagination.queryString+" and aa.rec_st != '0' order by aa.rec_upd_ts desc ";
		return memReviewDao.getPaginationMap(pagination, "aa", list);
	}
}
